﻿Imports System.Data.OleDb
Imports DTO
Public Class LopHocDao
    Public Shared Function LayDiemDat() As Double
        Dim cn As OleDbConnection = DataProvider.ConnectDB()
        Dim sql = "Select DiemHSDat from QUYDINH"
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        Dim diem As Double
        dr.Read()
        diem = dr("DiemHSDAT")
        cn.Close()
        Return diem
    End Function
    Public Shared Function DSTongKetMon(ByVal maMon As Integer, ByVal hocky As Integer) As DataTable
        Dim diemdat As Double = LayDiemDat()
        Dim cn As OleDbConnection = DataProvider.ConnectDB()
        Dim sql = "select l.TenLop , l.SiSo,count(*) as SoLuongDat,count(*)/SiSo as TiLe from KETQUA kq , LOP l where kq.MaLop = l.MaLop and l.MaLop = ? and kq.HocKy = ? and (kq.Diem15+Diem1Tiet*2+DiemCK*3)/6 >= ? group by l.TenLop , l.SiSo"
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@MaMon", OleDbType.Integer)
        cmd.Parameters.Add("@HocKy", OleDbType.Integer)
        cmd.Parameters.Add("@diemdat", OleDbType.Double)
        cmd.Parameters("@MaMon").Value = maMon
        cmd.Parameters("@HocKy").Value = hocky
        cmd.Parameters("@diemdat").Value = diemdat

        Dim dt As DataTable = New DataTable()
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
        da.Fill(dt)
        Return dt
    End Function
    Public Shared Function DSTongKetHK(ByVal hocky As Integer) As DataTable
        Dim diemdat As Double = LayDiemDat()
        Dim cn As OleDbConnection = DataProvider.ConnectDB()
        Dim sql = "select l.TenLop , l.SiSo,count(*) as SoLuongDat,count(*)/SiSo as TiLe from KETQUA kq , LOP l where kq.MaLop = l.MaLop and kq.HocKy = ? and (kq.Diem15+Diem1Tiet*2+DiemCK*3)/6 >= ? group by l.TenLop , l.SiSo"
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@hocky", OleDbType.Integer)
        cmd.Parameters.Add("@diemdat", OleDbType.Double)
        cmd.Parameters("@HocKy").Value = hocky
        cmd.Parameters("@diemdat").Value = diemdat
        Dim dt As DataTable = New DataTable()
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
        da.Fill(dt)
        Return dt
    End Function

    Public Function LayDSLopHoc() As List(Of LopHocDto)
        Dim ds As New List(Of LopHocDto)
        Dim cn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL As String = "Select *from LOP"
        Dim cmd As New OleDbCommand(strSQL, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        Dim lh As New LopHocDto()
        While (dr.Read())
            lh = New LopHocDto()
            lh.MaLop = dr("MaLop")
            lh.TenLop = dr("TenLop")
            ds.Add(lh)
        End While
        cn.Close()
        Return ds
    End Function
    Public Shared Function QuyDinh() As DataTable
        Dim cn As OleDbConnection = DataProvider.ConnectDB()
        Dim sql = "Select *from QUYDINH"
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dt As DataTable = New DataTable()
        Dim da As New OleDbDataAdapter(cmd)
        da.Fill(dt)
        Return dt
    End Function
    Public Shared Function ThayDoiQuyDinh(ByVal tuoitoithieu As Integer, ByVal tuoitoida As Integer, ByVal sisotoida As Integer, ByVal solop10 As Integer, ByVal solop11 As Integer, ByVal solop12 As Integer, ByVal sohktoida As Integer, ByVal somontoida As Integer, ByVal diemdat As Integer) As DataTable
        Dim cn As OleDbConnection = DataProvider.ConnectDB()
        Dim sql = "update QUYDINH set TuoiToiThieu=?,TuoiToiDa=?,SiSoToiDa=?,SoLopToiDa_10=?,SoLopToiDa_11=?,SoLopToiDa_12=?,SoHKToiDa=?,SoMonToiDa=?,DiemHSDat=?"
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@TuoiToiThieu", OleDbType.Integer)
        cmd.Parameters.Add("@TuoiToiDa", OleDbType.Integer)
        cmd.Parameters.Add("@SiSoToiDa", OleDbType.Integer)
        cmd.Parameters.Add("@SoLopToiDa_10", OleDbType.Integer)
        cmd.Parameters.Add("@SoLopToiDa_11", OleDbType.Integer)
        cmd.Parameters.Add("@SoLopToiDa_12", OleDbType.Integer)
        cmd.Parameters.Add("@SoHKToiDa", OleDbType.Integer)
        cmd.Parameters.Add("@SoMonToiDa", OleDbType.Integer)
        cmd.Parameters.Add("@DiemHSDat", OleDbType.Integer)

        cmd.Parameters("@TuoiToiThieu").Value = tuoitoithieu
        cmd.Parameters("@TuoiToiDa").Value = tuoitoida
        cmd.Parameters("@SiSoToiDa").Value = sisotoida
        cmd.Parameters("@SoLopToiDa_10").Value = solop10
        cmd.Parameters("@SoLopToiDa_11").Value = solop11
        cmd.Parameters("@SoLopToiDa_12").Value = solop12
        cmd.Parameters("@SoHKToiDa").Value = sohktoida
        cmd.Parameters("@SoMonToiDa").Value = somontoida
        cmd.Parameters("@DiemHSDat").Value = diemdat

        Dim dt As DataTable = New DataTable()
        Dim da As New OleDbDataAdapter(cmd)
        da.Fill(dt)
        cn.Close()
        Return dt

    End Function
End Class
